package keeto.com.vn.classified.database;

import java.util.ArrayList;
import java.util.Hashtable;

import keeto.com.vn.classified.config.IConfig;
import keeto.com.vn.classified.config.IConstant;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class BuiltinServiceManager implements IConstant {
	// private boolean isLogon;
	SabonaDBHelper dbHelper;
	// db object for write.
	SabonaDataBase db;
	// db object for read.
	// SQLiteDatabase dbr;
	static BuiltinServiceManager self = null;
	Context context;

	public Hashtable<String, String> getAccountInfo() {
		Hashtable<String, String> info = new Hashtable<String, String>();
		Cursor cursor = db.query("AccountInfo", null, null, null, null, null,
				null);
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			String key = cursor.getString(cursor.getColumnIndex("key"));
			String value = cursor.getString(cursor.getColumnIndex("info"));
			info.put(key, value);
		}
		cursor.close();
		return info;
	}

	public void saveAccountInfo(Hashtable<String, String> info) {
		if (info.size() < 1)
			return;
		SQLiteDatabase sdb = db.getDb();
		sdb.beginTransaction();
		try {
			sdb.delete("AccountInfo", null, null);
			for (String key : info.keySet()) {
				ContentValues cv = new ContentValues();
				cv.put("key", key);
				cv.put("info", info.get(key));
				cv.put("lastUpdate", -1);
				sdb.insert("AccountInfo", null, cv);
			}
			sdb.setTransactionSuccessful();
		} finally {
			sdb.endTransaction();
		}
	}

	public BuiltinServiceManager(Context context) {
		// setLogon(false);
		this.context = context;
		dbHelper = SabonaDBHelper.getInstance(context, false);
		db = SabonaDataBase.getInstance(dbHelper);
		// dbr = dbHelper.getReadableDatabase();
	}

	public static BuiltinServiceManager getInstance(Context context) {
		if (self == null) {
			self = new BuiltinServiceManager(context);
		}
		return self;
	}

	public BuiltinService getBuitinService() {
		BuiltinService service = null;
		if (db != null) {
			Cursor cursor = db.rawQuery("SELECT * FROM BuitinService", null);
			if (cursor.getCount() == 0) {
				// Log.i("DDAI", "no record in BuitinService");
				cursor.close();
			} else {
				service = new BuiltinService();
				cursor.moveToFirst();
				long serviceId = cursor.getLong(cursor
						.getColumnIndex("serviceLocalId"));
				service.setLocalId(cursor.getLong(cursor
						.getColumnIndex("localId")));
				service.setUserId(cursor.getString(cursor
						.getColumnIndex("userId")));
				service.setAuthKey(cursor.getString(cursor
						.getColumnIndex("Authkey")));
				service.setStatusMessage(cursor.getString(cursor
						.getColumnIndex("Status")));
				service.setSupperUser(cursor.getString(cursor
						.getColumnIndex("SuperUser")));
				service.setAreaID(cursor.getString(cursor
						.getColumnIndex("AreaID")));
				service.setDisplayName(cursor.getString(cursor
						.getColumnIndex("DisplayName")));
				service.setUserPhone(cursor.getString(cursor
						.getColumnIndex("UserPhone")));
				service.setUserEmail(cursor.getString(cursor
						.getColumnIndex("UserEmail")));
				service.setSex(cursor.getInt(cursor
						.getColumnIndex("Sex")));
				service.setDisplayAvatar(cursor.getBlob(cursor.getColumnIndex("displayAvatar")));
				cursor.close();
				cursor = db.query("Service", null, "localId=?",
						new String[] { String.valueOf(serviceId) }, null, null,
						null);
				if (cursor.getCount() == 0) {
					Log.d("DB", "no record in Service");
					cursor.close();
					return null;
				} else {
					cursor.moveToFirst();
					service.setStatusMessage(cursor.getString(cursor
							.getColumnIndex("status")));
					service.setDisplayName(cursor.getString(cursor
							.getColumnIndex("displayName")));
					service.setUserName(cursor.getString(cursor
							.getColumnIndex("username")));
					service.setPassword(cursor.getString(cursor
							.getColumnIndex("password")));
					service.setPresence(cursor.getString(cursor
							.getColumnIndex("presence")));
					cursor.close();
				}
			}

		}
		return service;
	}

	public boolean saveBuitinService(BuiltinService service) {
		boolean result = false;
		if (db != null) {
			Cursor cursor = db.rawQuery("SELECT * FROM buitinService", null);
			if (cursor.getCount() == 0) {
				cursor.close();
				try {
					ContentValues cv = new ContentValues();
					cv.put("status", service.getStatusMessage());
					cv.put("displayName", service.getDisplayName());
					cv.put("username", service.getUserName());
					cv.put("password", service.getPassword());
					cv.put("presence", service.getPresence());

					long serviceId = db.insert("service", "username", cv);
					if (serviceId != -1) {
						// insert table:buitinService
						cv = new ContentValues();
						cv.put("serviceLocalId", serviceId);
						cv.put("DisplayName",
								service.getDisplayName());
						cv.put("userId", service.getUserId());
						cv.put("Authkey", service.getAuthKey());
						cv.put("Status", service.getStatusMessage());
						cv.put("SuperUser", service.getSupperUser());
						cv.put("UserPhone", service.getUserPhone());
						cv.put("UserEmail", service.getUserEmail());
						cv.put("AreaID", service.getAreaID());
						cv.put("Sex", service.getSex());
						if (service.getDisplayAvatar() != null)
							cv.put("displayAvatar", service.getDisplayAvatar());
						long id = db.insert("BuitinService", "serviceLocalId", cv);
					} else {
						if (IConfig.DEBUG_MODE)
							Log.d("DDAI", "insert Table:service fail");
					}
					result = true;
				} catch (SQLException e) {
					if (IConfig.DEBUG_MODE)
						Log.e("SQLException", e.getMessage());
				}
			} else {
				// update record
				try {
					cursor.moveToFirst();
					long serviceId = cursor.getLong(cursor
							.getColumnIndex("serviceLocalId"));
					cursor.close();
					ContentValues cv = new ContentValues();
					cv.put("status", service.getStatusMessage());
					cv.put("displayName", service.getDisplayName());
					cv.put("username", service.getUserName());
					cv.put("password", service.getPassword());
					cv.put("presence", service.getPresence());
					int rows = db.update("service", cv, "localId=?",
							new String[] { String.valueOf(serviceId) });
					if (rows < 1) {
						serviceId = db.insert("service", null, cv);

					}
					ContentValues idcv = new ContentValues();
					idcv.put("serviceLocalId", serviceId);
					idcv.put("userId", service.getUserId());
					rows = db.update("BuitinService", idcv, null, null);
					if (rows < 1) {
						db.insert("BuitinService", null, idcv);
					}
					if (service.getDisplayAvatar() != null) {
						// update table :buitinService
						ContentValues values = new ContentValues();

						values.put("serviceLocalId", serviceId);
						values.put("DisplayName",
								service.getDisplayName());
						values.put("userId", service.getUserId());
						values.put("Authkey", service.getAuthKey());
						values.put("Status", service.getStatusMessage());
						values.put("SuperUser", service.getSupperUser());
						values.put("UserPhone", service.getUserPhone());
						values.put("UserEmail", service.getUserEmail());
						values.put("AreaID", service.getAreaID());
						values.put("Sex", service.getSex());
						if (service.getDisplayAvatar() != null)
							values.put("displayAvatar", service.getDisplayAvatar());
					int a =	db.update("BuitinService", values, "serviceLocalId=?",	new String[] { serviceId+""});
					result = true;
	
					}
				} catch (Exception e) {
					if (IConfig.DEBUG_MODE)
						Log.e("SQLException", e.getMessage());
				}
			}
			cursor.close();
		} else {
			if (IConfig.DEBUG_MODE)
				Log.e("DDAI", "can't get the SQLiteDatabase");
		}

		return result;
	}

	public boolean saveProductID(String productID, long time){
		boolean result = false;
		if (db != null) {
			Cursor cursor = db.rawQuery("SELECT * FROM ProductHistory", null);
			if (cursor.getCount() == 0) {
				cursor.close();
				ContentValues cv = new ContentValues();
				cv.put("ProductID", productID);
				cv.put("lastUpdate", time);
				long serviceId = db.insert("ProductHistory", null, cv);
			} else {
				cursor.moveToFirst();
				for (int i=0; i<cursor.getCount() ;i++){
					String product = cursor.getString(cursor.getColumnIndex("ProductID"));
					if (product.equals(productID)){
						ContentValues cv = new ContentValues();
						cv.put("ProductID", productID);
						cv.put("lastUpdate", time);	
						db.delete("ProductHistory", "ProductID =?", new String[] { String.valueOf(product)});
						long serviceId = db.insert("ProductHistory", null, cv);
						result = true;
						break;
					}
				}
				cursor.moveToFirst();
				if (result != true){
					ContentValues cv = new ContentValues();
					cv.put("ProductID", productID);
					cv.put("lastUpdate", time);
					long serviceId = db.insert("ProductHistory", null, cv);
					result = true;
				}
				cursor.close();

			}
		} else {
			if (IConfig.DEBUG_MODE)
				Log.e("DDAI", "can't get the SQLiteDatabase");
		}
		Log.e("ProductHistory", "ID: " + productID + " Time  : " + time);
		return result;

	}

	public ArrayList<String> getproductHistory(){
		ArrayList<String> listID = new ArrayList<String>();
		if (db != null) {
			Cursor cursor = db.rawQuery("SELECT * FROM ProductHistory", null);
			if (cursor.getCount() == 0) {
				// Log.i("DDAI", "no record in BuitinService");
				cursor.close();
			} else {
				Log.e("ProductHistory", "SIZE " + cursor.getCount() );
				cursor.moveToFirst();
				if (cursor.getCount() > 20){
					for (int j=0; j< (cursor.getCount() - 20);j++){
						cursor.moveToFirst();
						String productid = cursor.getString(cursor.getColumnIndex("ProductID"));
						db.delete("ProductHistory", "ProductID =?", new String[] { String.valueOf(productid)});
						cursor.moveToNext();
					}
				}
				cursor.close();
				cursor = db.rawQuery("SELECT * FROM ProductHistory", null);
				cursor.moveToFirst();
				for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
					listID.add(cursor.getString(cursor.getColumnIndex("ProductID")));
					Log.e("ProductHistory", " SIze " + cursor.getCount() + " "  +cursor.getString(cursor.getColumnIndex("localId")) + " ListID: " + cursor.getString(cursor.getColumnIndex("ProductID")) + " TIME " + cursor.getString(cursor.getColumnIndex("lastUpdate")));
				}
				cursor.close();
			}
		}

		return listID;

	}

	public void changeDisplayName(String name) {
		Cursor cursor = db
				.query("BuitinService", new String[] { "serviceLocalId" },
						null, null, null, null, null);
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			long localId = cursor.getLong(cursor
					.getColumnIndex("serviceLocalId"));
			cursor.close();
			if (localId != -1) {
				ContentValues cv = new ContentValues();
				cv.put("displayName", name);
				db.update("Service", cv, "localId=?",
						new String[] { String.valueOf(localId) });
			}
		}
	}

	public void changeStatusMessage(String status) {
		Cursor cursor = db
				.query("BuitinService", new String[] { "serviceLocalId" },
						null, null, null, null, null);
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			long localId = cursor.getLong(cursor
					.getColumnIndex("serviceLocalId"));
			cursor.close();
			if (localId != -1) {
				ContentValues cv = new ContentValues();
				cv.put("status", status);
				db.update("Service", cv, "localId=?",
						new String[] { String.valueOf(localId) });
			}
		}
	}

	public void changePresence(String presence) {
		Cursor cursor = db
				.query("BuitinService", new String[] { "serviceLocalId" },
						null, null, null, null, null);
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			long localId = cursor.getLong(cursor
					.getColumnIndex("serviceLocalId"));
			cursor.close();
			if (localId != -1) {
				ContentValues cv = new ContentValues();
				cv.put("presence", presence);
				db.update("Service", cv, "localId=?",
						new String[] { String.valueOf(localId) });
			}
		}
	}


	public void deleteBuitinService(String name) {
		try {
			db.delete("BuitinService",null,null);
			db.delete("service", null, null);
			db.delete("ProductHistory", null, null);

		} catch (SQLException e) {
			Log.e("DB", e.getMessage());
		}
	}

	/**
	 * get buitinService display picture.if localId wrong,return null
	 */
	public byte[] getServiceDisplayPicture(long localId) {
		byte[] picture = null;
		if (localId == UNINITIALIZED_LONG) {
			return picture;
		} else {
			Cursor c = db.query("DisplayPicture", null, "localId=?",
					new String[] { String.valueOf(localId) }, null, null, null);
			if (c.getCount() > 0) {
				c.moveToFirst();
				picture = c.getBlob(c.getColumnIndex("data"));
			}
			c.close();
		}

		return picture;
	}

	public long saveServiceDisplayPicture(byte[] picture, int size) {
		Log.d("saveServiceDisplayPicture", "size" + size);
		long id = UNINITIALIZED_LONG;
		if (picture.length <= 0) {
			return id;
		}
		ContentValues cv = new ContentValues();
		cv.put("size", size);
		cv.put("data", picture);
		try {
			id = db.insert("DisplayPicture", "data", cv);
		} catch (SQLException e) {
			Log.d("db", e.getMessage());
		}  
		Cursor c = db.query("BuitinService",
				new String[] { "displayPictureLocalId" }, null, null, null,
				null, null);
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			db.delete("DisplayPicture", "localId=?", new String[] { String
					.valueOf(c.getLong(c
							.getColumnIndex("displayPictureLocalId"))) });
		}
		c.close();
		cv = new ContentValues();
		cv.put("displayPictureLocalId", id);
		try {
			db.update("buitinService", cv, null, null);
		} catch (SQLException e) {
			Log.d("DB", e.getMessage());
			return UNINITIALIZED_LONG;
		}
		return id;
	}



	public void closeDB() {
		db.close();
		dbHelper.close();
	}

	@Override
	protected void finalize() throws Throwable {
		closeDB();
		super.finalize();
	}

	public String getMyID() {
		String result = UNINITIALIZED_STRING;
		Cursor c = db
				.query("BuitinService", null, null, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			result = c.getString(c.getColumnIndex("userId"));
		}
		c.close();
		return result;
	}

	/**
	 * @return get all Roster's <key,value> in Hashtable<RemoteId, localId>
	 */
	public Hashtable<String, Integer> getRosterIds() {

		Cursor cursor = db.query("BuitinRoster"
				, new String[]{"remoteId", "localId"}
				, null, null, null, null, null);
		Hashtable<String, Integer> idTable = new Hashtable<String, Integer>();
		if(cursor.getCount() > 0){
			cursor.moveToFirst();
			for(int i=0; i<cursor.getCount(); i++){
				idTable.put(cursor.getString(0), cursor.getInt(1));
				cursor.moveToNext();
			}
			Log.i("getRosterIds", "ids:"+idTable);
			cursor.close();
			return idTable;
		}else{
			cursor.close();
			return null;
		}
	}


}
